<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class Linkslistsips extends Model
{
    protected $table = 'links_lists_ips';
    protected $primaryKey = 'id';

    public static function creatLinksIps($links_id,$province,$city,$system,$device,$browser,$ip,$status,$code)
    {
        return self::insert(["links_id"=>$links_id,"province"=>$province,"city"=>$city,"system"=>$system,"device"=>$device,"browser"=>$browser,"ip"=>$ip,"status"=>$status,"code"=>$code,"created_at"=>time()]);
    }

    public static function getLinksLastIps($links_id,$ip)
    {
        return self::select("id","status","active")->where('links_id', $links_id)->where('ip', $ip)->orderBy("id","DESC")->first();
    }

    public static function updateLinksIpsStatus($id,$status,$active)
    {
        return self::where('id', $id)->update(["status"=>$status,"active"=>$active]);
    }

    public static function getLinkslistsips($links_id,$status,$province,$date_start,$date_end)
    {
        $query = self::where('links_id', $links_id);
        if (!empty($status)) $query->where('status', $status);
        if (!empty($province)) $query->where('province', $province);
        if (!empty($date_start)) $query->where('created_at','>=', $date_start);
        if (!empty($date_end)) $query->where('created_at','<=', $date_end);
        return $query->orderBy("id","DESC")->paginate(25);
    }

    public static function getLinkslistsipsCount($links_id,$status,$province,$date_start,$date_end)
    {
        $query = self::where('links_id', $links_id);
        if (!empty($status)) $query->where('status', $status);
        if (!empty($province)) $query->where('province', $province);
        if (!empty($date_start)) $query->where('created_at','>=', $date_start);
        if (!empty($date_end)) $query->where('created_at','<=', $date_end);
        return $query->count("id");
    }

    public static function getSystemDatas($links_id)
    {
        return self::select(DB::raw('count(id) as system_count, system'))
                     ->where('links_id', $links_id)
                     ->distinct('ip')
                     ->groupBy('system')
                     ->get();
    }

    public static function getBrowserDatas($links_id)
    {
        return self::select(DB::raw('count(id) as browser_count, browser'))
                     ->where('links_id', $links_id)
                     ->distinct('ip')
                     ->groupBy('browser')
                     ->get();
    }

    public static function getDeviceDatas($links_id)
    {
        return self::select(DB::raw('count(id) as device_count, device'))
                     ->where('links_id', $links_id)
                     ->distinct('ip')
                     ->groupBy('device')
                     ->get();
    }

    public static function getProvinceDatas($links_id)
    {
        return self::select(DB::raw('count(id) as province_count, province'))
                     ->where('links_id', $links_id)
                     ->distinct('ip')
                     ->groupBy('province')
                     ->get();
    }

    public static function getDefaultDatas($links_id,$start_time,$end_time,$datebatch)
    {
    	if ($datebatch == "day") {
    		$unit = "%Y-%m-%d";
    	} else {
    		$unit = "%H";
    	}
        return self::select(DB::raw('count(id) as count, DATE_FORMAT(FROM_UNIXTIME(created_at),"'.$unit.'") as `date`,status'))
                     ->where('links_id', $links_id)
                     ->whereBetween('created_at', [$start_time,$end_time])
                     ->distinct('ip')
                     ->groupBy('date')
                     ->groupBy('status')
                     ->get();
    }

    public static function getAllViewsDatas($links_id)
    {
        return self::select(DB::raw('count(id) as count,status'))
                     ->where('links_id', $links_id)
                     ->distinct('ip')
                     ->groupBy('status')
                     ->get();
    }

    public static function getAllListsViewsDatas($links_ids)
    {
        return self::select(DB::raw('count(id) as count,status,links_id'))
                     ->whereIn('links_id', $links_ids)
                     ->groupBy('links_id')
                     ->groupBy('status')
                     ->get();
    }

    public static function getTodayLinksListsIpsHour($links_id,$created_at)
    {
        return DB::select('select count("id") as count,FROM_UNIXTIME(created_at,"%H") as hour,status from links_lists_ips where links_id = :links_id and FROM_UNIXTIME(created_at,"%Y-%m-%d") = :created_at group by status,hour', ['links_id' => $links_id,'created_at' => $created_at]);
    }

}
